package com.situ.personal.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.situ.personal.entity.Department;
import com.situ.personal.util.JDBCUtil;
import com.situ.personal.vo.DepartmentCountVO;

public class DepartmentDao {
	public List<Department> selectAll() {
		Connection connection = null;
	    PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<Department> list = new ArrayList<Department>();
		
	    try {
			connection = JDBCUtil.getConnection();
			String sql = "SELECT `id`,`name` FROM `department`";
			preparedStatement = connection.prepareStatement(sql);
			System.out.println(preparedStatement);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				
				Department department = new Department(id, name);
				list.add(department);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
	    for (Department department : list) {
			System.out.println(department);
		}
		return list;
	}
	
	public List<DepartmentCountVO> count() {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<DepartmentCountVO> list = new ArrayList<DepartmentCountVO>();
		try {
			connection = JDBCUtil.getConnection();
			String sql = "SELECT d.name,COUNT(*) FROM `user` AS u INNER JOIN `department` AS d ON u.department_id=d.id GROUP BY d.id";
			preparedStatement = connection.prepareStatement(sql);
			System.out.println(preparedStatement);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				String name = resultSet.getString("name");
				int count = resultSet.getInt("count(*)");
				
				DepartmentCountVO departmentCountVO = new DepartmentCountVO(name, count);
				list.add(departmentCountVO);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return list;
	}

}
